/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.rbh.risk.dao.impl;

import com.rbh.risk.dao.RiskDataDao;
import com.rbh.risk.entity.Privilege;
import com.rbh.risk.entity.Profile;
import com.rbh.risk.entity.RiskData;
import com.rbh.risk.entity.SeverityLevel;
import com.rbh.risk.entity.Workgroup;
import com.rbh.risk.model.SummaryLevelModel;
import com.rbh.risk.model.TopReportModel;
import com.rbh.risk.support.SessionFactorySupport;
import com.rbh.risk.util.Constants;
import com.rbh.risk.util.DateUtil;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Junction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.sql.JoinType;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Repository;

@Repository("riskDataDao")
public class RiskDataDaoImpl extends SessionFactorySupport implements RiskDataDao {

    private List<SummaryLevelModel> pullDataObject(List<Object> list) throws DataAccessException {
        List<SummaryLevelModel> results = null;
        try {
            results = new ArrayList<>();
            for (Object item : list) {
                Object[] objs = (Object[]) item;
                SummaryLevelModel sumLv = new SummaryLevelModel();
                String data = objs[0] + "";
                sumLv.setWorkgroupId(Integer.parseInt(data.substring(0, data.indexOf("."))));
                sumLv.setWorkgroupName(data.substring(data.indexOf(".") + 1));
                sumLv.setSummary(Integer.parseInt(objs[1] + ""));
                sumLv.setIdCategoryA(1);
                sumLv.setCategoryA(Integer.parseInt(objs[2] + ""));
                sumLv.setIdCategoryB(2);
                sumLv.setCategoryB(Integer.parseInt(objs[3] + ""));
                sumLv.setIdCategoryC(3);
                sumLv.setCategoryC(Integer.parseInt(objs[4] + ""));
                sumLv.setIdCategoryD(4);
                sumLv.setCategoryD(Integer.parseInt(objs[5] + ""));
                sumLv.setIdCategoryE(5);
                sumLv.setCategoryE(Integer.parseInt(objs[6] + ""));
                sumLv.setIdCategoryF(6);
                sumLv.setCategoryF(Integer.parseInt(objs[7] + ""));
                sumLv.setIdCategoryG(7);
                sumLv.setCategoryG(Integer.parseInt(objs[8] + ""));
                sumLv.setIdCategoryH(8);
                sumLv.setCategoryH(Integer.parseInt(objs[9] + ""));
                sumLv.setIdCategoryI(9);
                sumLv.setCategoryI(Integer.parseInt(objs[10] + ""));
                results.add(sumLv);
            }
        } catch (Exception ex) {
            throw ex;
        }
        return results;
    }

    @Override
    public void insert(RiskData risk) throws DataAccessException {
        try {
            super.getSessionFactory().getCurrentSession().save(risk);
        } catch (Exception ex) {
            throw ex;
        }
    }

    @Override
    public void update(RiskData risk) throws DataAccessException {
        try {
            super.getSessionFactory().getCurrentSession().update(risk);
        } catch (Exception ex) {
            throw ex;
        }
    }

    @Override
    public void delete(RiskData risk) throws DataAccessException {
        try {
            super.getSessionFactory().getCurrentSession().delete(risk);
        } catch (Exception ex) {
            throw ex;
        }
    }

    @Override
    public RiskData getById(String riskId) throws DataAccessException {
        RiskData result = null;
        try {
            result = (RiskData) super.getSessionFactory().getCurrentSession().get(RiskData.class, riskId);
        } catch (Exception ex) {
            throw ex;
        }
        return result;
    }

    @Override
    public List<RiskData> getAll() throws DataAccessException {
        List<RiskData> results = null;
        try {
            Query query = super.getSessionFactory().getCurrentSession().createQuery("from " + RiskData.class.getName());
            results = query.list();
        } catch (Exception ex) {
            throw ex;
        }
        return results;
    }

    @Override
    public List<RiskData> getByProfile(HashMap<Privilege, String> privileges, Profile profile, Date dateFrom, Date dateTo) throws DataAccessException {
        List<RiskData> results = null;
        try {
            List<Profile> profileList = new ArrayList<>();
            if (!privileges.containsValue(Constants.$_YOURSELF)
                    && !privileges.containsValue(Constants.$_FRONTEND)) {
                for (Workgroup w : profile.getWorkgroupList()) {
                    profileList.addAll(w.getProfileList());
                    for (Workgroup w1 : w.getWorkgroupList()) {
                        profileList.addAll(w1.getProfileList());
                        for (Workgroup w2 : w1.getWorkgroupList()) {
                            profileList.addAll(w2.getProfileList());
                        }
                    }
                }
            } else {
                profileList.add(profile);
            }
            Criteria criteria = super.getSessionFactory().getCurrentSession().createCriteria(RiskData.class);
            if (privileges.containsValue(Constants.$_FRONTEND)) {
                criteria.add(Restrictions.between("dataAccidentDatetime", dateFrom, DateUtil.adjustDate(dateTo, 1, 0, 0)));
            } else {
                if (privileges.containsValue(Constants.$_YOURSELF)) {
                    criteria.add(Restrictions.conjunction()
                            .add(Restrictions.in("profileCreate", profileList))
                            .add(Restrictions.between("dataAccidentDatetime", dateFrom, DateUtil.adjustDate(dateTo, 1, 0, 0))));
                } else {
                    criteria.add(Restrictions.conjunction()
                            .add(Restrictions.disjunction()
                            .add(Restrictions.in("profileCreate", profileList))
                            .add(Restrictions.in("workgroup", profile.getWorkgroupList()))
                            .add(Restrictions.in("workgroupConnected", profile.getWorkgroupList())))
                            .add(Restrictions.between("dataAccidentDatetime", dateFrom, DateUtil.adjustDate(dateTo, 1, 0, 0))));
                }
            }
            criteria.addOrder(Order.desc("dataAccidentDatetime"));
            results = criteria.list();
        } catch (Exception ex) {
            throw ex;
        }
        return results;
    }

    @Override
    public List<SummaryLevelModel> getMajorSummaryLevels(HashMap<Privilege, String> privileges, Profile profile, Date from, Date to) throws DataAccessException {
        List<SummaryLevelModel> results = null;
        String strList = "";
        String profileId = "";
        String workgroupList = "";
        int lv = 0;
        try {
            for (Workgroup i : profile.getWorkgroupList()) {
                strList += (i.getWorkgroupId() + ",");
                lv = i.getWorkgroupLevel().getWorkgroupLevelId().intValue();
            }

            if (strList != null && !strList.isEmpty()) {
                workgroupList = strList.substring(0, strList.length() - 1);
            }

            if (privileges.containsValue(Constants.$_YOURSELF)) {
                profileId = profile.getProfileId();
            }

            results = new ArrayList<>();
            Query query = super.getSessionFactory().getCurrentSession().createSQLQuery("CALL GetMajorSumLevelReport(?,?,?,?,?)");
            query.setParameter(0, lv);
            query.setParameter(1, workgroupList);
            query.setParameter(2, from);
            query.setParameter(3, DateUtil.adjustDate(to, 1, 0, 0));
            query.setParameter(4, profileId);

            results = this.pullDataObject(query.list());
        } catch (HibernateException | NumberFormatException ex) {
            throw ex;
        }
        return results;
    }

    @Override
    public List<SummaryLevelModel> getMinorSummaryLevels(Workgroup workgroup, Date from, Date to) throws DataAccessException {
        List<SummaryLevelModel> results = null;
        try {
            results = new ArrayList<>();
            Query query = super.getSessionFactory().getCurrentSession().createSQLQuery("CALL GetMinorSumLevelReport(?,?,?)");
            query.setParameter(0, workgroup.getWorkgroupId());
            query.setParameter(1, from);
            query.setParameter(2, DateUtil.adjustDate(to, 1, 0, 0));

            results = this.pullDataObject(query.list());
        } catch (HibernateException | NumberFormatException ex) {
            throw ex;
        }
        return results;
    }

    @Override
    public List<RiskData> getRiskDetailList(Workgroup workgroup, SeverityLevel level, Profile profile, Date from, Date to) throws DataAccessException {
        List<RiskData> results = null;
        try {
            List<Workgroup> workgroups = new ArrayList<>();
            switch (workgroup.getWorkgroupLevel().getWorkgroupLevelId()) {
                case 1:
                    workgroups.add(workgroup);
                    break;
                case 2:
                    for (Workgroup w : workgroup.getWorkgroupList()) {
                        workgroups.add(w);
                    }
                    break;
                case 3:
                    for (Workgroup w : workgroup.getWorkgroupList()) {
                        for (Workgroup w1 : w.getWorkgroupList()) {
                            workgroups.add(w1);
                        }
                    }
                    break;
            }

            Criteria criteria = super.getSessionFactory().getCurrentSession().createCriteria(RiskData.class, "data");
            criteria.createAlias("data.workgroup", "wg", JoinType.LEFT_OUTER_JOIN);
            criteria.createAlias("data.workgroupConnected", "wgc", JoinType.LEFT_OUTER_JOIN);
            criteria.createAlias("data.severityLevelDetail", "s");

            Criterion conWorkgroup = Restrictions.disjunction()
                    .add(Restrictions.in("data.workgroup", workgroups))
                    .add(Restrictions.in("data.workgroupConnected", workgroups));
            Criterion conLevel = Restrictions.eq("s.severityLevel", level);
            Criterion conProfile = Restrictions.eq("data.profileCreate", profile);
            Criterion conDate = Restrictions.between("data.dataAccidentDatetime", from, DateUtil.adjustDate(to, 1, 0, 0));

            if (level != null) {
                if (profile != null) {
                    criteria.add(Restrictions.conjunction().add(conWorkgroup).add(conLevel).add(conProfile).add(conDate));
                } else {
                    criteria.add(Restrictions.conjunction().add(conWorkgroup).add(conLevel).add(conDate));
                }
            } else {
                if (profile != null) {
                    criteria.add(Restrictions.conjunction().add(conWorkgroup).add(conProfile).add(conDate));
                } else {
                    criteria.add(Restrictions.conjunction().add(conWorkgroup).add(conDate));
                }
            }
            criteria.addOrder(Order.desc("data.dataCreateDatetime"));
            results = criteria.list();
        } catch (Exception ex) {
            throw ex;
        }
        return results;
    }

    @Override
    public List<TopReportModel> getTopReport() throws DataAccessException {
        return this.getTopReport(null);
    }

    @Override
    public List<TopReportModel> getTopReport(Integer limit) throws DataAccessException {
        List<TopReportModel> results = null;
        try {
            Criteria criteria = super.getSessionFactory().getCurrentSession().createCriteria(RiskData.class, "data");
            criteria.createAlias("data.profileCreate", "profile");
            criteria.createAlias("profile.opduser", "user");

            ProjectionList pros = Projections.projectionList();
            pros.add(Projections.property("profile.profileId"));
            pros.add(Projections.property("user.loginname"));
            pros.add(Projections.property("user.name"));
            pros.add(Projections.count("data.dataId"), "summary");
            pros.add(Projections.groupProperty("profile.profileId"));
            criteria.setProjection(pros);

            criteria.addOrder(Order.desc("summary"));

            if (limit != null) {
                criteria.setMaxResults(limit.intValue());
            }

            List<Object> rs = criteria.list();

            results = new ArrayList<>();

            for (Iterator it = rs.iterator(); it.hasNext();) {
                Object[] objs = (Object[]) it.next();
                TopReportModel tr = new TopReportModel();
                tr.setProfileId(objs[0] + "");
                tr.setUsername(objs[1] + "");
                tr.setFullname(objs[2] + "");
                tr.setPoint(Integer.parseInt(objs[3] + ""));
                results.add(tr);
            }

        } catch (HibernateException | NumberFormatException ex) {
            throw ex;
        }
        return results;
    }

    @Override
    public List<RiskData> getByFilter(int first, int pageSize, String sortField, boolean sortOrder, Map<String, String> filters, HashMap<Privilege, String> privileges, Profile profile) throws DataAccessException {
        List<RiskData> results = null;
        try {
//            List<Profile> profileList = new ArrayList<>();
//            if (!privileges.containsValue(Constants.$_YOURSELF)
//                    && !privileges.containsValue(Constants.$_FRONTEND)) {
//                for (Workgroup w : profile.getWorkgroupList()) {
//                    profileList.addAll(w.getProfileList());
//                    for (Workgroup w1 : w.getWorkgroupList()) {
//                        profileList.addAll(w1.getProfileList());
//                        for (Workgroup w2 : w1.getWorkgroupList()) {
//                            profileList.addAll(w2.getProfileList());
//                        }
//                    }
//                }
//            } else {
//                profileList.add(profile);
//            }

            Criteria criteria = super.getSessionFactory().getCurrentSession()
                    .createCriteria(RiskData.class, "data");
            criteria.createAlias("data.workgroup", "workgroup");
            criteria.createAlias("data.workgroupConnected", "workgroupConnected", JoinType.LEFT_OUTER_JOIN);
            criteria.createAlias("data.category", "category");
            criteria.createAlias("data.mainEvent", "mainEvent");
            criteria.createAlias("data.subEvent", "subEvent");
            criteria.createAlias("data.mainCause", "mainCause");
            criteria.createAlias("data.subCause", "subCause");
            criteria.createAlias("data.severityLevelDetail", "severityLevelDetail");

            Junction junction = new Conjunction();

            for (Iterator<String> it = filters.keySet().iterator(); it.hasNext();) {
                String filterProperty = it.next();
                String filterValue = filters.get(filterProperty);
                System.out.println("FilterProperty --> " + filterProperty);
                System.out.println("FilterValue -----> " + filterValue);

                if (filterProperty.equalsIgnoreCase("dateFilter")) {
                    String[] spl = filterValue.split(",");
                    junction.add(Restrictions.between("data.dataAccidentDatetime",
                            DateUtil.resetTime(DateUtil.convertStringToDate(spl[0])),
                            DateUtil.adjustDate(DateUtil.convertStringToDate(spl[1]), 1, 0, 0)));
                } else {
                    junction.add(Restrictions.eq(filterProperty, filterValue));
                }
            }

            criteria.add(junction);

            System.out.println("SortField --> " + sortField);

            if (sortField != null) {
                if (sortOrder) {
                    criteria.addOrder(Order.asc(sortField));
                } else {
                    criteria.addOrder(Order.desc(sortField));
                }
            }
            criteria.setFirstResult(first);
            criteria.setMaxResults(pageSize);
            results = criteria.list();
        } catch (HibernateException | ParseException ex) {
        } finally {
            System.out.println("Query record size --> " + results.size());
        }
        return results;
    }

    @Override
    public Integer getMaxResult(Map<String, String> filters, HashMap<Privilege, String> privileges, Profile profile) throws DataAccessException {
        Long result = 0L;
        try {
//            List<Profile> profileList = new ArrayList<>();
//            if (!privileges.containsValue(Constants.$_YOURSELF)
//                    && !privileges.containsValue(Constants.$_FRONTEND)) {
//                for (Workgroup w : profile.getWorkgroupList()) {
//                    profileList.addAll(w.getProfileList());
//                    for (Workgroup w1 : w.getWorkgroupList()) {
//                        profileList.addAll(w1.getProfileList());
//                        for (Workgroup w2 : w1.getWorkgroupList()) {
//                            profileList.addAll(w2.getProfileList());
//                        }
//                    }
//                }
//            } else {
//                profileList.add(profile);
//            }

            Criteria criteria = super.getSessionFactory().getCurrentSession().createCriteria(RiskData.class, "data")
                    .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                    .setProjection(Projections.rowCount());

            Junction junction = new Conjunction();

            for (Iterator<String> it = filters.keySet().iterator(); it.hasNext();) {
                String filterProperty = it.next();
                String filterValue = filters.get(filterProperty);

                if (filterProperty.equalsIgnoreCase("dateFilter")) {
                    String[] spl = filterValue.split(",");
                    junction.add(Restrictions.between("data.dataAccidentDatetime",
                            DateUtil.resetTime(DateUtil.convertStringToDate(spl[0])),
                            DateUtil.adjustDate(DateUtil.convertStringToDate(spl[1]), 1, 0, 0)));
                } else {
                    junction.add(Restrictions.eq(filterProperty, filterValue));
                }
            }

            criteria.add(junction);

            List<?> results = criteria.list();
            if (results != null && results.size() > 0) {
                result = (Long) results.get(0);
            }
        } catch (HibernateException | ParseException ex) {
        } finally {
            System.out.println("Result of size --> " + result.intValue());
        }
        return result.intValue();
    }
}
